using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace DocLibApp
{
    /// <summary>
    /// Document Library System functions
    /// </summary>
    class doc_system
    {
        #region System Code

        public static List<string> GetCodeGroupList()
        {
            List<string> GroupList = new List<string>();

            string sql = "SELECT DISTINCT [CODE_GROUP] FROM " + def.tb_System_Code + " ORDER BY [CODE_GROUP]";
            System.Collections.ArrayList alist = FormMain.DocLib_SQLite.GetRowsWithDataReader(sql, false, false);

            foreach (object obj in alist)
            {
                if (obj is List<string>)
                {
                    List<string> aRow = obj as List<string>;
                    string GroupName = aRow[0];

                    GroupList.Add(GroupName);
                }
            }

            return GroupList;
        }
        /// <summary>
        /// get code description from code group and key
        /// </summary>
        /// <param name="Code_Group"></param>
        /// <param name="Key"></param>
        /// <returns></returns>
        public static string GetCodeDescription(string Code_Group, string Key)
        {
            string res = "";

            List<string> FieldList = new List<string>();
            FieldList.Add("DESC");

            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["CODE_GROUP"] = Code_Group;
            ht["CODE"] = Key;

            FormMain.DocLib_QB.TableName = def.tb_System_Code;
            FormMain.DocLib_QB.Get_Select(FieldList, ht);
            res = FormMain.DocLib_QB.GetDataString();

            return res;            
        }

        public static bool KeyExistInGroup(string key, string group)
        {
            bool bFlag = false;

            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["CODE_GROUP"] = group;
            ht["CODE"] = key;

            FormMain.DocLib_QB.TableName = def.tb_System_Code;
            FormMain.DocLib_QB.Get_Select(ht);

            if (FormMain.DocLib_SQLite.GetRecordCountUseCriteria(FormMain.DocLib_QB.SQL) > 0)
                bFlag = true;

            return bFlag;
        }

        public static void SetCodeDescription(string Code_Group, string Key, string Desc)
        {
            FormMain.DocLib_QB.TableName = def.tb_System_Code;

            //if key does not exist for the group, creates it instead of updating it
            if (KeyExistInGroup(Key, Code_Group))
            {
                System.Collections.Hashtable ht = new System.Collections.Hashtable();
                ht["DESC"] = Desc;

                System.Collections.Hashtable htWhere = new System.Collections.Hashtable();
                htWhere["CODE_GROUP"] = Code_Group;
                htWhere["CODE"] = Key;

                FormMain.DocLib_QB.Get_Update(ht, htWhere);
                FormMain.DocLib_QB.RunQuery();
            }
            else
            {
                System.Collections.Hashtable ht = new System.Collections.Hashtable();
                ht["CODE_GROUP"] = Code_Group;
                ht["CODE"] = Key;
                ht["DESC"] = Desc;

                FormMain.DocLib_QB.Get_Insert(ht);
                FormMain.DocLib_QB.RunQuery();
            }
        }
        #endregion //System Code

        #region Table Column Definition
        /// <summary>
        /// Use this function to get all the columns from the database and create the column definition table entries
        /// should only run once to populate all columns
        /// </summary>
        public static void GenerateTableColumnsDefinition()
        {
            //consider remove all entries in the table column definition table before running

            System.Collections.ArrayList TableList = FormMain.DocLib_SQLite.GetDataTableStructureForAllTables();
            foreach (object obj in TableList)
            {
                if (obj is DataTable)
                {
                    DataTable dt = obj as DataTable;
                    System.Collections.ArrayList ColumnList = FormMain.DocLib_SQLite.GetColumnsFromTable(dt);
                    foreach (object oj in ColumnList)
                    {
                        if (oj is DataColumn)
                        {
                            DataColumn dc = oj as DataColumn;

                            string TableName = dc.Table.TableName;

                            string FieldName = dc.ColumnName;

                            System.Collections.Hashtable ht = new System.Collections.Hashtable();
                            ht["TABLENAME"] = TableName;
                            ht["FIELDNAME"] = FieldName;
                            ht["DESCRIPTION"] = FieldName;

                            FormMain.DocLib_QB.TableName = def.tb_table_columns;
                            FormMain.DocLib_QB.Get_Insert(ht);
                            FormMain.DocLib_QB.RunQuery();
                        }
                    }

                }
            }
        }

        /// <summary>
        /// set description for a column in a table
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="column"></param>
        /// <param name="desc"></param>
        public static void SetColumnDescription(string tablename, string column, string desc)
        {
            string old_desc = GetColumnDescription(tablename, column);

            //only update if description is same as the old description
            if (desc != old_desc)
            {
                System.Collections.Hashtable ht = new System.Collections.Hashtable();
                ht["DESCRIPTION"] = desc;

                System.Collections.Hashtable htWhere = new System.Collections.Hashtable();
                htWhere["TABLENAME"] = tablename;
                htWhere["FIELDNAME"] = column;

                FormMain.DocLib_QB.TableName = def.tb_table_columns;
                FormMain.DocLib_QB.Get_Update(ht, htWhere);
                FormMain.DocLib_QB.RunQuery();
            }
        }

        /// <summary>
        /// get Column Description for a column in a tablename
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static string GetColumnDescription(string tablename, string column)
        {
            string desc = ""; 
            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["TABLENAME"] = tablename;
            ht["FIELDNAME"] = column;

            List<string> FieldList = new List<string>();
            FieldList.Add("DESCRIPTION");

            FormMain.DocLib_QB.TableName = def.tb_table_columns;
            FormMain.DocLib_QB.Get_Select(FieldList, ht);
            desc = FormMain.DocLib_SQLite.GetDataString(FormMain.DocLib_QB.SQL);

            if (string.IsNullOrEmpty(desc))
                desc = column;//default return the column name if there is nothing

            return desc;
        }
        #endregion // Table Column Definition

        #region System List
        /// <summary>
        /// Add a string to a list, given limit, list group
        /// tested
        /// </summary>
        /// <remarks>
        /// With the limit, it keeps the list in certain number, when a new item is added to the list,
        /// the oldest created item would be removed if the limit was reached.
        /// </remarks>
        /// <param name="List_Group"></param>
        /// <param name="ItemToBeAdded"></param>
        /// <param name="Limit"></param>
        public static void AddToList(string List_Group, string ItemToBeAdded, string ItemDesc, int Limit)
        {
            //first check to see if the item exists already
            List<string> list = GetAList_OnlyID(List_Group);

            if (!list.Contains(ItemToBeAdded))
            {
                if (list.Count == Limit)
                {
                    string DeleteItem = list[list.Count-1];
                    DeleteFromList(List_Group, DeleteItem);
                }

                System.Collections.Hashtable ht = new System.Collections.Hashtable();
                ht["NAME"] = ItemToBeAdded;
                
                if (!string.IsNullOrEmpty(ItemDesc))
                    ht["DESC"] = ItemDesc;

                ht["LIST_GROUP"] = List_Group;
                ht["DT_CREATE"] = SQLiteDB.DateTimeToString(System.DateTime.Now);

                FormMain.DocLib_QB.TableName = def.tb_list;
                FormMain.DocLib_QB.Get_Insert(ht);
                FormMain.DocLib_QB.RunQuery();
            }            
        }

        /// <summary>
        /// Delete a item from the list
        /// tested
        /// </summary>
        /// <param name="List_Group"></param>
        /// <param name="ItemToBeDeleted"></param>
        public static void DeleteFromList(string List_Group, string ItemToBeDeleted)
        {
            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["NAME"] = ItemToBeDeleted;
            ht["LIST_GROUP"] = List_Group;

            FormMain.DocLib_QB.TableName = def.tb_list;
            FormMain.DocLib_QB.Get_Delete(ht);
            FormMain.DocLib_QB.RunQuery();
        }

        /// <summary>
        /// get a list and return ht[id] = desc
        /// </summary>
        /// <param name="List_Group"></param>
        /// <returns></returns>
        public static System.Collections.Hashtable GetAList(string List_Group)
        {
            System.Collections.Hashtable htList = new System.Collections.Hashtable();

            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["LIST_GROUP"] = List_Group;

            List<string> FieldList = new List<string>();
            FieldList.Add("NAME");
            FieldList.Add("DESC");

            FormMain.DocLib_QB.TableName = def.tb_list;
            FormMain.DocLib_QB.Get_Select(FieldList, ht);

            string sql = FormMain.DocLib_QB.SQL;

            //order by is necessary to put the oldest item at the end of the list
            sql = sql + " ORDER BY [DT_CREATE] DESC";

            System.Collections.ArrayList alist = FormMain.DocLib_SQLite.GetRowsWithDataReader(sql, false, false);

            foreach (object obj in alist)
            {
                if (obj is List<string>)
                {
                    List<string> list = obj as List<string>;
                    string item = list[0];
                    string desc = list[1];

                    htList[item] = desc;
                }
            }

            return htList;
        }

        /// <summary>
        /// retrieve a list based on given List_group
        /// tested
        /// </summary>
        /// <param name="List_Group"></param>
        /// <returns></returns>
        public static List<string> GetAList_OnlyID(string List_Group)
        {
            List<string> MyList = new List<string>();

            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["LIST_GROUP"] = List_Group;

            List<string> FieldList = new List<string>();
            FieldList.Add("NAME");
            FieldList.Add("DESC");

            FormMain.DocLib_QB.TableName = def.tb_list;
            FormMain.DocLib_QB.Get_Select(FieldList, ht);

            string sql = FormMain.DocLib_QB.SQL;

            //order by is necessary to put the oldest item at the end of the list
            sql = sql + " ORDER BY [DT_CREATE] DESC";

            System.Collections.ArrayList alist = FormMain.DocLib_SQLite.GetRowsWithDataReader(sql, false, false);

            foreach (object obj in alist)
            {
                if (obj is List<string>)
                {
                    List<string> list = obj as List<string>;
                    string item = list[0];
                    string desc = list[1];

                    MyList.Add(item);
                }
            }

            return MyList;
        }

        #endregion //EO System List

        #region registration

        public static string RegisterDocLibrary(string user, string company, string num, string key)
        {
            string res = "";
            res += user;
            res += System.Environment.NewLine + company;
            res += System.Environment.NewLine + num;
            res += System.Environment.NewLine + key;

            if (System.IO.File.Exists(def.FileName_DefaultLicense))
                System.IO.File.Delete(def.FileName_DefaultLicense);

            util.WriteTextFile(def.FileName_DefaultLicense, res);

            return "Registration information is updated successfully.";
        }

        /// <summary>
        /// Get license information from license file
        /// </summary>
        /// <returns>List<string> user, company, number of license, key</returns>
        public static List<string> GetLicenseInfo(string FileName)
        {
            List<string> list = new List<string>();

            string res = util.ReadTextFileIntoString(FileName);

            string[] items = res.Split(new char[] { '\n' });
            foreach (string aitem in items)
            {
                if (aitem.Trim() != "")
                    list.Add(aitem.Trim());
            }

            return list;
        }
        #endregion // registration

        #region System Log
        /// Log system log messages
        /// uses table SYSTEM_LOG:
        /// - EVENT_ID
        /// - MESSAGE
        /// - DT_EVENT
        /// - TYPE
        public static void WriteSystemLog(string msg, string type)
        {
            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            ht["MESSAGE"] = msg;
            ht["TYPE"] = type;
            FormMain.DocLib_QB.TableName = def.tb_System_Log;
            FormMain.DocLib_QB.Get_Insert(ht);
            FormMain.DocLib_QB.RunQuery();
        }
        #endregion // EO System Log
    }
}
